Comparison Functions ¶

Contents

Comparison Functions
- COALESCE
- DECODE
- GREATEST
- IF
- IFNULL, NVL
- ISNULL
- LEAST
- NULLIF
- NVL2

COALESCE ¶

COALESCE(expression [, expression ] ...)¶

The COALESCE function has more than one expression as an argument. If the first argument is non-NULL, the corresponding value is returned if it is NULL, the second argument is returned. If all expressions which have an argument are NULL, NULL is returned. Therefore, this function is generally used to replace NULL with other default value.

Parameters:	expression -- Specifies more than one expression. Their types must be comparable each other.
Return type:	determined with the type of the arguments

Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.

CHAR < VARCHAR
BIT < VARBIT
SHORT < INT < BIGINT < NUMERIC < FLOAT < DOUBLE
DATE < TIMESTAMP < DATETIME

For example, if a type of a is INT, b, BIGINT, c, SHORT, and d, FLOAT, then COALESCE (a, b, c, d) returns a FLOAT type. If a type of a is INTEGER, b, DOULBE , c, FLOAT, and d, TIMESTAMP, then COALESCE (a, b, c, d) returns a VARCHAR type.

COALESCE (a, b) works the same as the CASE expression as follows:

CASE WHEN a IS NOT NULL
THEN a
ELSE b
END

SELECT * FROM case_tbl;

            a
=============
            1
            2
            3
         NULL

--substituting a default value 10.0000 for a NULL value
SELECT a, COALESCE(a, 10.0000) FROM case_tbl;

            a  coalesce(a, 10.0000)
===================================
            1  1.0000
            2  2.0000
            3  3.0000
         NULL  10.0000

DECODE ¶

DECODE(expression, search, result [, search, result]* [, default])¶

As well as a CASE expression, the DECODE function performs the same functionality as the IF ... THEN ... ELSE statement. It compares the expression argument with search argument, and returns the result corresponding to search that has the same value. It returns default if there is no search with the same value, and returns NULL if default is omitted. An expression argument and a search argument to be comparable should be same or convertible each other. The number of digits after the decimal point is determined to display all significant figures including valid number of all result.

Parameters:	expression,search -- expressions that are comparable with each other result -- the value to be returned when matched default -- the value to be retuned when no match is found
Return type:	determined with the type of result and default

DECODE(a, b, c, d, e, f) has the same meaning as the CASE expression below.

CASE WHEN a = b THEN c
WHEN a = d THEN e
ELSE f
END

SELECT * FROM case_tbl;

            a
=============
            1
            2
            3
         NULL

--Using DECODE function to compare expression and search values one by one
SELECT a, DECODE(a, 1, 'one', 2, 'two', 'other') FROM case_tbl;

            a  decode(a, 1, 'one', 2, 'two', 'other')
===================================
            1  'one'
            2  'two'
            3  'other'
         NULL  'other'

--result types are converted to a single type containing all of significant figures
SELECT a, DECODE(a, 1, 1, 2, 1.2345, 1.234567890) FROM case_tbl;

            a  decode(a, 1, 1, 2, 1.2345, 1.234567890)
===================================
            1  1.000000000
            2  1.234500000
            3  1.234567890
         NULL  1.234567890

--an error occurs when result types are not convertible
SELECT a, DECODE(a, 1, 'one', 2, 'two', 1.2345) FROM case_tbl;

ERROR: Cannot coerce 'one' to type double.

GREATEST ¶

GREATEST(expression [, expression] ...)¶

The GREATEST function compares more than one expression specified as parameters and returns the greatest value. If only one expression has been specified, the expression is returned because there is no expression to be compared with.

Therefore, more than one expression that is specified as parameters must be of the type that can be compared with each other. If the types of the specified parameters are identical, so are the types of the return values; if they are different, the type of the return value becomes a convertible common data type.

That is, the GREATEST function compares the values of column 1, column 2 and column 3 in the same row and returns the greatest value while the MAX function compares the values of column in all result rows and returns the greatest value.

Parameters:	expression -- Specifies more than one expression. Their types must be comparable each other. One of the arguments is NULL, NULL is returned.
Return type:	same as that of the argument

The following example shows how to retrieve the number of every medals and the highest number that Korea won in the demodb database.

SELECT gold, silver , bronze, GREATEST (gold, silver, bronze)
FROM participant
WHERE nation_code = 'KOR';

         gold       silver       bronze  greatest(gold, silver, bronze)
=======================================================================
            9           12            9                              12
            8           10           10                              10
            7           15            5                              15
           12            5           12                              12
           12           10           11                              12

IF ¶

IF(expression1, expression2, expression3)¶

The IF function returns expression2 if the value of the arithmetic expression specified as the first parameter is TRUE, or expression3 if the value is FALSE or NULL. expression2 and expression3 which are returned as a result must be the same or of a convertible common type. If one is explicitly NULL, the result of the function follows the type of the non-NULL parameter.

Parameters:	expression1 -- comparison expression expression2 -- the value to be returned when expression1 is true expression3 -- the value to be returned when expression1 is not true
Return type:	type of expression2 or expression3

IF(a, b, c) has the same meaning as the CASE expression in the following example:

CASE WHEN a IS TRUE THEN b
ELSE c
END

SELECT * FROM case_tbl;

            a
=============
            1
            2
            3
         NULL

--IF function returns the second expression when the first is TRUE
SELECT a, IF(a=1, 'one', 'other') FROM case_tbl;

            a   if(a=1, 'one', 'other')
===================================
            1  'one'
            2  'other'
            3  'other'
         NULL  'other'

--If function in WHERE clause
SELECT * FROM case_tbl WHERE IF(a=1, 1, 2) = 1;

            a
=============
            1

IFNULL, NVL ¶

IFNULL(expr1, expr2)¶

NVL(expr1, expr2)¶

The IFNULL function is working like the NVL function; however, only the NVL function supports collection type as well. The IFNULL function (which has two arguments) returns expr1 if the value of the first expression is not NULL or returns expr2, otherwise.

Parameters:	expr1 -- expression expr2 -- the value to be returned when expr1 is NULL
Return type:	determined with the type of expr1 and expr2

Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.

CHAR < VARCHAR
BIT < VARBIT
SHORT < INT < BIGINT < NUMERIC < FLOAT < DOUBLE
DATE < TIMESTAMP < DATETIME

For example, if a type of a is INT and b is BIGINT, then IFNULL (a, b) returns a BIGINT type. If a type of a is INTEGER and b is TIMESTAMP, then IFNULL (a, b) returns a VARCHAR type.

IFNULL(a, b) or NVL(a, b) has the same meaning as the CASE expression below.

CASE WHEN a IS NULL THEN b
ELSE a
END

SELECT * FROM case_tbl;

            a
=============
            1
            2
            3
         NULL

--returning a specific value when a is NULL
SELECT a, NVL(a, 10.0000) FROM case_tbl;

            a  nvl(a, 10.0000)
===================================
            1  1.0000
            2  2.0000
            3  3.0000
         NULL  10.0000

--IFNULL can be used instead of NVL and return values are converted to the string type
SELECT a, IFNULL(a, 'UNKNOWN') FROM case_tbl;

            a   ifnull(a, 'UNKNOWN')
===================================
            1  '1'
            2  '2'
            3  '3'
         NULL  'UNKNOWN'

ISNULL ¶

ISNULL(expression)¶

The ISNULL function performs a comparison to determine if the result of the expression specified as an argument is NULL. The function returns 1 if it is NULL or 0 otherwise. You can check if a certain value is NULL. This function is working like the ISNULL expression.

Parameters:	expression -- An arithmetic function that has a single-value column, path expression (ex.: tbl_name.col_name), constant value is specified.
Return type:	INT

--Using ISNULL function to select rows with NULL value
SELECT * FROM condition_tbl WHERE ISNULL(salary);

           id  name                  dept_name                  salary
======================================================================
            7  'Brown     '          'account'                    NULL

LEAST ¶

LEAST(expression [, expression] ...)¶

The LEAST function compares more than one expression specified as parameters and returns the smallest value. If only one expression has been specified, the expression is returned because there is no expression to be compared with.

Therefore, more than one expression that is specified as parameters must be of the type that can be compared with each other. If the types of the specified parameters are identical, so are the types of the return values; if they are different, the type of the return value becomes a convertible common data type.

That is, the LEAST function compares the values of column 1, column 2 and column 3 in the same row and returns the smallest value while the MIN() compares the values of column in all result rows and returns the smallest value.

Parameters:	expression -- Specifies more than one expression. Their types must be comparable each other. One of the arguments is NULL, NULL is returned.
Return type:	same as that of the argument

The following example shows how to retrieve the number of every medals and the lowest number that Korea won in the demodb database.

SELECT gold, silver , bronze, LEAST(gold, silver, bronze) FROM participant
WHERE nation_code = 'KOR';

         gold       silver       bronze  least(gold, silver, bronze)
====================================================================
            9           12            9                            9
            8           10           10                            8
            7           15            5                            5
           12            5           12                            5
           12           10           11                           10

NULLIF ¶

NULLIF(expr1, expr2)¶

The NULLIF function returns NULL if the two expressions specified as the parameters are identical, and returns the first parameter value otherwise.

Parameters:	expr1 -- expression to be compared with expr2 expr2 -- expression to be compared with expr1
Return type:	type of expr1

NULLIF (a, b) is the same of the CASE expression.

CASE
WHEN a = b THEN NULL
ELSE a
END

SELECT * FROM case_tbl;

            a
=============
            1
            2
            3
         NULL

--returning NULL value when a is 1
SELECT a, NULLIF(a, 1) FROM case_tbl;

            a  nullif(a, 1)
===========================
            1          NULL
            2             2
            3             3
         NULL          NULL

--returning NULL value when arguments are same
SELECT NULLIF (1, 1.000)  FROM db_root;

  nullif(1, 1.000)
======================
  NULL

--returning the first value when arguments are not same
SELECT NULLIF ('A', 'a')  FROM db_root;

  nullif('A', 'a')
======================
  'A'

NVL2 ¶

NVL2(expr1, expr2, expr3)¶

Three parameters are specified for the NVL2 function. The second expression (expr2) is returned if the first expression (expr1) is not NULL; the third expression (expr3) is returned if it is NULL.

Parameters:	expr1 -- expression expr2 -- the value to be returned when expr1 is not NULL expr3 -- the value to be returned when expr1 is NULL
Return type:	determined with the type of expr1, expr2 and expr3

Operation is performed by converting the type of every argument into that with the highest priority. If there is an argument whose type cannot be converted, the type of every argument is converted into a VARCHAR type. The following list shows priority of conversion based on input argument type.

CHAR < VARCHAR
BIT < VARBIT
SHORT < INT < BIGINT < NUMERIC < FLOAT < DOUBLE
DATE < TIMESTAMP < DATETIME

For example, if a type of a is INT, b, BIGINT, and c, SHORT, then NVL2 (a, b, c) returns a BIGINT type. If a type of a is INTEGER, b, DOUBLE, and c, TIMESTAMP, then NVL2 (a, b, c) returns a VARCHAR type.

SELECT * FROM case_tbl;

            a
=============
            1
            2
            3
         NULL

--returning a specific value of INT type
SELECT a, NVL2(a, a+1, 10.5678) FROM case_tbl;

            a  nvl2(a, a+1, 10.5678)
====================================
            1                      2
            2                      3
            3                      4
         NULL                     11